﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//


using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Data.EffiProzDB;
using System.Transactions;

namespace EffiProzDBTest.ADO.Net
{
    /// <summary>
    /// Summary description for AdoNetProviderTest
    /// </summary>
    [TestClass]
    public class AdoNetProviderTest
    {

        string connString = "Type=Memory ; Data Source=TestCaseDB; User=sa; Password=;";

        public AdoNetProviderTest()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

       

        [TestMethod]
        public void ADONetBasic()
        {
            string connString = "Type=Memory ; Data Source=testadob; User=sa; Password=;";
            string sql = "CREATE TABLE TEST(ID INT, NAME VARCHAR);";
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;

                conn.Open();
                int count = command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'irantha'); INSERT INTO TEST(ID , NAME ) VALUES(NULL,'nuwan');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                command.CommandText = "SELECT * FROM TEST;";
                DbDataReader reader = command.ExecuteReader();
                System.Diagnostics.Debug.WriteLine(reader.FieldCount);
                while (reader.Read())
                {
                    if (reader.IsDBNull(0))
                        System.Diagnostics.Debug.WriteLine("ID= " + " Name=" + reader.GetString(1));
                    else
                        System.Diagnostics.Debug.WriteLine("ID= " + reader.GetInt32(0) + " Name=" + reader.GetString(1));
                }

                command = conn.CreateCommand();
                command.CommandText = "Call Sqrt(25);";
                double sqrt = (double)command.ExecuteScalar();
                Assert.AreEqual(5, sqrt);
            }
        }

        [TestMethod]
        public void ADONetBasicFile()
        {
            string filePath = "../../../EffiProzDBTest/Data/FileDB";
            string connString = "Type=File ; Data Source=" + filePath + "; User=sa; Password=;";
            string sql = "DROP TABLE TEST IF EXISTS; CREATE CACHED TABLE TEST(ID INT, NAME VARCHAR);";
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;

                conn.Open();
                int count = command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'irantha'); INSERT INTO TEST(ID , NAME ) VALUES(NULL,'nuwan');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                command.CommandText = "SELECT * FROM TEST;";
                DbDataReader reader = command.ExecuteReader();
                System.Diagnostics.Debug.WriteLine(reader.FieldCount);
                while (reader.Read())
                {
                    if (reader.IsDBNull(0))
                        System.Diagnostics.Debug.WriteLine("ID= " + " Name=" + reader.GetString(1));
                    else
                        System.Diagnostics.Debug.WriteLine("ID= " + reader.GetInt32(0) + " Name=" + reader.GetString(1));
                }

                command = conn.CreateCommand();
                command.CommandText = "SHUTDOWN;";
                command.ExecuteNonQuery();
                
            }
        }

        internal void PrintDT(DataTable tbl)
        {
            for (int i = 0; i < tbl.Columns.Count; i++)
            {
                System.Diagnostics.Debug.Write(tbl.Columns[i] + "\t");
            }
            System.Diagnostics.Debug.WriteLine("");


            for (int j = 0; j < tbl.Rows.Count; j++)
            {
                for (int i = 0; i < tbl.Columns.Count; i++)
                {
                    System.Diagnostics.Debug.Write(tbl.Rows[j][i] + "\t");
                }
                System.Diagnostics.Debug.WriteLine("");
            }
        }

        [TestMethod]
        public void ADONetSchema()
        {
            string connString = "Type=Memory ; Data Source=testsch; User=sa; Password=;";
            string sql = "CREATE TABLE TEST(ID INT,ID2 INT, NAME VARCHAR, PRIMARY KEY(ID), UNIQUE (ID2));";
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;

                conn.Open();
                int count = command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO TEST(ID , ID2, NAME ) VALUES(1, 100,'irantha'); INSERT INTO TEST(ID ,ID2, NAME ) VALUES(2, 500,'subash');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                DataTable tb = conn.GetSchema("Tables", new string[] { "PUBLIC" });
                Assert.IsNotNull(tb);
                Assert.AreEqual(1, tb.Rows.Count);

                tb = conn.GetSchema("Columns", new string[] { null,"PUBLIC", "TEST", "ID" });
                Assert.AreEqual(1, tb.Rows.Count);
                tb = conn.GetSchema("Columns", new string[] { null, "PUBLIC", "TEST", "NAME" });
                Assert.AreEqual(1, tb.Rows.Count);

                tb = conn.GetSchema("PRIMARYKEYS", new string[] { null, "PUBLIC", "TEST" });
                Assert.AreEqual(1, tb.Rows.Count);

                tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST" });
                Assert.AreEqual(2, tb.Rows.Count);

                tb = conn.GetSchema("INDEXES", new string[] { null, "PUBLIC", "TEST",null, "true" });
                Assert.AreEqual(2, tb.Rows.Count);

                tb = conn.GetSchema("SCHEMAS");
                Assert.AreEqual(2, tb.Rows.Count);

                tb = conn.GetSchema("TYPES");
                Assert.IsTrue(tb.Rows.Count > 0);
                // PrintDT(tb);

                command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM TEST";
                DbDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo);
                DataTable columnSchemaTbl = reader.GetSchemaTable();
                Assert.IsTrue((bool)columnSchemaTbl.Rows[0]["IsKey"]);
                Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
                Assert.IsTrue((bool)columnSchemaTbl.Rows[1]["IsUnique"]);

                command = conn.CreateCommand();
                command.CommandText = "SELECT * FROM TEST";
                reader = command.ExecuteReader();
                columnSchemaTbl = reader.GetSchemaTable();
                Assert.IsFalse((bool)columnSchemaTbl.Rows[0]["IsKey"]);
                Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsKey"]);
                Assert.IsFalse((bool)columnSchemaTbl.Rows[1]["IsUnique"]);

                tb = new DataTable("Test");
                tb.Load(reader);


                tb = conn.GetSchema("TABLEPRIVILEGES");
                Assert.IsTrue(tb.Rows.Count > 0);

                tb = conn.GetSchema("METADATACOLLECTIONS");
                Assert.IsTrue(tb.Rows.Count > 0);

                tb = conn.GetSchema("PROCEDURES");
                Assert.IsTrue(tb.Rows.Count > 0);

                tb = conn.GetSchema("PROCEDUREPARAMETERS");
                Assert.IsTrue(tb.Rows.Count > 0);

                tb = conn.GetSchema("TRIGGERS", new string[] { "PUBLIC", "Dummy" });
                Assert.IsTrue(tb.Rows.Count == 0);

                tb = conn.GetSchema("VIEWS");
                Assert.IsTrue(tb.Rows.Count == 0);

                command = conn.CreateCommand();
                command.CommandText = "CREATE VIEW v1 AS SELECT ID,NAME FROM TEST";
                command.ExecuteNonQuery();

                tb = conn.GetSchema("VIEWS");
                Assert.IsTrue(tb.Rows.Count == 1);

                command = conn.CreateCommand();
                command.CommandText = "CREATE TABLE TEST2(IDD INT,IDD2 INT, NAME VARCHAR, FOREIGN KEY(IDD2)" +
                                                                             " REFERENCES TEST(ID));";
                command.ExecuteNonQuery();

                tb = conn.GetSchema("EXPORTEDKEYS");
                Assert.IsTrue(tb.Rows.Count == 1);

                tb = conn.GetSchema("IMPORTEDKEYS");
                Assert.IsTrue(tb.Rows.Count == 1);

                tb = conn.GetSchema("FOREIGNKEYS");
                Assert.IsTrue(tb.Rows.Count == 1);

                // PrintDT(tb);

            }
        }

        [TestMethod]
        public void ADOParameters()
        {
            string connString = "Type=Memory ; Data Source=testpara; User=sa; Password=;";
            string sql = "CREATE TABLE TEST(ID INT, NAME VARCHAR,created DATETIME,NAME2 CHAR);";
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;

                conn.Open();
                int count = command.ExecuteNonQuery();
                command.CommandText = "INSERT INTO TEST(ID , NAME, created, NAME2 ) VALUES(@ID , @Name, @Created, @NAME);";

                DbParameter id = command.CreateParameter();
                id.ParameterName = "@ID";
                id.Value = 1;
                command.Parameters.Add(id);

                DbParameter name = command.CreateParameter();
                name.ParameterName = "@NAME";
                name.Value = "van";
                command.Parameters.Add(name);

                DbParameter created = command.CreateParameter();
                created.ParameterName = "@CREATED";
                created.Value = DateTime.Now;
                command.Parameters.Add(created);

                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                command.CommandText = "SELECT * FROM TEST;";
                DbDataReader reader = command.ExecuteReader();
                System.Diagnostics.Debug.WriteLine(reader.FieldCount);
                while (reader.Read())
                {
                    if (reader.IsDBNull(0))
                        System.Diagnostics.Debug.WriteLine("ID= " + " Name=" + reader.GetString(1));
                    else
                        System.Diagnostics.Debug.WriteLine("ID= " + reader.GetInt32(0) + " Name=" + reader.GetString(1) +
                            " CREATE:" + reader.GetDateTime(2) + " Name2:" + reader.GetString(3));
                }

            }
        }


        [TestMethod]
        public void ADONetTransactions3()
        {
            string connString = "Type=Memory ; Data Source=testtx3; User=sa; Password=; AutoCommit=false;";
            string countSql = "SELECT count(*) FROM TEST;";
            int count;
            DbTransaction tx0, tx1, tx2, tx3;
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = "CREATE TABLE TEST(ID INT, NAME VARCHAR);";
                conn.Open();
                count = command.ExecuteNonQuery();

                tx0 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'car');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                tx1 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(2,'bus');";
                count = command.ExecuteNonQuery();

                tx2 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(3,'bus');";
                count = command.ExecuteNonQuery();

                tx3 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(4,'bus');";
                count = command.ExecuteNonQuery();

                tx2.Commit();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(4, count);

                tx3.Rollback();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(4, count);


                tx1.Commit();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(4, count);

                tx0.Rollback();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(0, count);
            }
        }

        [TestMethod]
        public void ADONetTransactions2()
        {
            string connString = "Type=Memory ; Data Source=testtx2; User=sa; Password=; AutoCommit=false;";
            string countSql = "SELECT count(*) FROM TEST;";
            int count;
            DbTransaction tx1, tx2;
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = "CREATE TABLE TEST(ID INT, NAME VARCHAR);";
                conn.Open();
                count = command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'car');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                tx1 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(2,'bus');";
                count = command.ExecuteNonQuery();

                tx2 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(3,'bus');";
                count = command.ExecuteNonQuery();

                tx2.Commit();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(3, count);

                tx1.Rollback();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(1, count);
            }
        }

        [TestMethod]
        public void ADONetTransactions()
        {
            string connString = "Type=Memory ; Data Source=testtx; User=sa; Password=; AutoCommit=false;";
            string countSql = "SELECT count(*) FROM TEST;";
            int count;
            DbTransaction tx1;
            using (EffiProzDBConnection conn = new EffiProzDBConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = "CREATE TABLE TEST(ID INT, NAME VARCHAR);";
                conn.Open();
                count = command.ExecuteNonQuery();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(1,'car');";
                count = command.ExecuteNonQuery();
                Assert.AreEqual(count, 1);

                tx1 = conn.BeginTransaction();

                command.CommandText = "INSERT INTO TEST(ID , NAME ) VALUES(2,'bus');";
                count = command.ExecuteNonQuery();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(2, count);

                tx1.Rollback();

                command.CommandText = countSql;
                count = (int)command.ExecuteScalar();
                Assert.AreEqual(1, count);
            }
        }

        private void CreateTable(DbConnection cnn, string tableName)
        {

            string sql = "CREATE TABLE " + tableName +
                          "(ID bigint identity primary key , Field1 integer, Field2 Double, \"Fiëld3\" VARCHAR(50), \"Fiæld4\" CHAR(10), Field5 Date, Field6 Binary)";
            DbCommand command = cnn.CreateCommand();
            command.CommandText = sql;
            command.ExecuteNonQuery();
        }


        [TestMethod]
        public void BinaryInsert()
        {
            string tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection cnn = new EffiProzDBConnection(connString))
            {
                cnn.Open();
                CreateTable(cnn, tableName);
                using (DbCommand cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO " + tableName + "(Field6) VALUES(@bin)";
                    DbParameter Field6 = cmd.CreateParameter();

                    byte[] b = new byte[4000];
                    b[0] = 1;
                    b[100] = 2;
                    b[1000] = 3;
                    b[2000] = 4;
                    b[3000] = 5;

                    Field6.ParameterName = "@bin";
                    Field6.Value = b;

                    cmd.Parameters.Add(Field6);
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT Field6 FROM " + tableName + ";";
                    byte[] rb = (byte[])cmd.ExecuteScalar();


                    Assert.AreEqual(b.Length, rb.Length);
                    Assert.AreEqual(b[0], rb[0]);
                    Assert.AreEqual(b[100], rb[100]);
                    Assert.AreEqual(b[1000], rb[1000]);
                    Assert.AreEqual(b[2000], rb[2000]);
                    Assert.AreEqual(b[3000], rb[3000]);

                }
            }
        }

        private void InsertTable(DbConnection _cnn, string tableName)
        {
            using (DbCommand cmd = _cnn.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO " + tableName + "(Field1, Field2, \"Fiëld3\", \"Fiæld4\", Field5) VALUES(1, 3.14159, 'Fiëld3', 'Fiæld4', '2005-01-01 13:49:00')";
                cmd.ExecuteNonQuery();
            }
        }

        [TestMethod]
        public void CoersionTest()
        {
            string tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                CreateTable(_cnn, tableName);
                InsertTable(_cnn, tableName);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Field1, Field2, \"Fiëld3\", \"Fiæld4\", Field5, 'A', 1, 1 + 1, 3.14159 FROM "
                        + tableName;
                    using (DbDataReader rd = cmd.ExecuteReader())
                    {

                        DataTable dt = rd.GetSchemaTable();
                        PrintDT(dt);

                        if (rd.Read())
                        {
                            object Field1 = rd.GetInt32(0);
                            object Field2 = rd.GetDouble(1);
                            object Field3 = rd.GetString(2);
                            object Field4 = rd.GetString(3).TrimEnd();
                            object Field5 = rd.GetDateTime(4);

                            // The next statement should cause an exception
                            try
                            {
                                Field1 = rd.GetString(0);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }

                            try
                            {
                                Field2 = rd.GetString(1);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }
                            Field3 = rd.GetString(2);
                            Field4 = rd.GetString(3);

                            Field1 = rd.GetInt32(0);

                            try
                            {
                                Field2 = rd.GetInt32(1);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }

                            try
                            {
                                Field3 = rd.GetInt32(2);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }

                            try
                            {
                                Field4 = rd.GetInt32(3);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }
                            try
                            {
                                Field5 = rd.GetInt32(4);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }

                            try
                            {
                                Field3 = rd.GetDecimal(2);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }
                            catch (FormatException)
                            {
                            }
                            try
                            {
                                Field4 = rd.GetDecimal(3);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }
                            catch (FormatException)
                            {
                            }
                            try
                            {
                                Field5 = rd.GetDecimal(4);
                                Assert.Fail("Should have failed type checking!");
                            }
                            catch (InvalidCastException)
                            {
                            }
                            catch (FormatException)
                            {
                            }
                        }
                        else Assert.Fail("No data in table");
                    }
                }
            }
        }

        [TestMethod]
        public void ParseTest()
        {
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();

                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.Parameters.Add(cmd.CreateParameter());
                    cmd.Parameters[0].Value = 1;

                    cmd.Parameters.Add(cmd.CreateParameter());
                    cmd.Parameters[1].Value = 1;


                    cmd.CommandText = @"CREATE TEMP TABLE A(ID INT, BID INT);CREATE TEMP TABLE B(ID INT, MYVAL VARCHAR);
                            INSERT INTO A (ID, BID) VALUES(2, 1);
                            INSERT INTO B (ID, MYVAL) VALUES(1,'TEST');
                            ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "select *, (select 1 as c from b where b.id = a.bid) from a;";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                    }                 
                }
            }
        }

        /// <summary>
        /// Make sure a datareader can run even if the parent command is disposed, and that the connection is closed
        /// by the datareader when it is finished.
        /// </summary>
        [TestMethod]
        public void DataReaderCleanup()
        {
            string tableName = TestHelper.GetNewTableName();
            DbConnection newcnn = new EffiProzDBConnection(connString);
            DbCommand cmd = newcnn.CreateCommand();

            try
            {
                if (newcnn.State != ConnectionState.Open)
                    newcnn.Open();

                CreateTable(newcnn, tableName);
                InsertTable(newcnn, tableName);

                cmd.CommandText = "SELECT 1, 2, 3 FROM " + tableName;
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    cmd.Dispose(); // Dispose of the command while an open reader is active ... should still be able to read
                    try
                    {
                        reader.Read();
                    }
                    catch
                    {
                        throw new Exception("Unable to read from a DataReader!");
                    }

                    if (reader.GetInt32(0) != 1 || reader.GetInt32(1) != 2 || reader.GetInt32(2) != 3)
                        throw new Exception("Unexpected return values from reader!");

                    reader.Close(); // Close the reader, and check if the connection is closed

                    if (newcnn.State != ConnectionState.Closed)
                        throw new Exception("DataReader failed to cleanup!");
                }
            }
            finally
            {
                cmd.Dispose();
                newcnn.Dispose();
            }
        }


        [TestMethod]
        public void DataTypeTest()
        {
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                DateTime now = DateTime.Now;

                using (DbCommand cmd = _cnn.CreateCommand())
                {

                    cmd.CommandText = "create table datatypetest(id bigint identity primary key, myvalue other, datetimevalue datetime, decimalvalue decimal(38,18))";

                    cmd.ExecuteNonQuery();

                    System.Globalization.CultureInfo oldculture = System.Threading.Thread.CurrentThread.CurrentCulture;
                    System.Globalization.CultureInfo olduiculture = System.Threading.Thread.CurrentThread.CurrentUICulture;

                    // Insert using a different current culture
                    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-ES");
                    System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture;

                    try
                    {
                        cmd.CommandText = "insert into datatypetest(myvalue, datetimevalue, decimalvalue) values(@p1,@p2,@p3)";
                        DbParameter p1 = cmd.CreateParameter();
                        DbParameter p2 = cmd.CreateParameter();
                        DbParameter p3 = cmd.CreateParameter();

                        cmd.Parameters.Add(p1);
                        cmd.Parameters.Add(p2);
                        cmd.Parameters.Add(p3);

                        p1.ParameterName = "@p1";
                        p2.ParameterName = "@p2";
                        p3.ParameterName = "@p3";

                        p1.Value = (long)1;
                        p2.Value = new DateTime(1753, 1, 1);
                        p3.Value = (Decimal)1.05;
                        cmd.ExecuteNonQuery();

                        p1.ResetDbType();
                        p2.ResetDbType();
                        p3.ResetDbType();

                        p1.Value = "One";
                        p2.Value = "2001-01-01";
                        p3.Value = (Decimal)1.0;
                        cmd.ExecuteNonQuery();

                        p1.ResetDbType();
                        p2.ResetDbType();
                        p3.ResetDbType();

                        p1.Value = 1.01;
                        p2.Value = now;
                        p3.Value = (Decimal)9.91;
                        cmd.ExecuteNonQuery();

                        // Read using a different current culture
                        System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture;

                        cmd.CommandText = "select myvalue, datetimevalue, decimalvalue from datatypetest";
                        cmd.Parameters.Clear();
                        using (DbDataReader reader = cmd.ExecuteReader())
                        {
                            for (int n = 0; n < 3; n++)
                            {
                                reader.Read();
                                if (reader.GetValue(1).GetType() != reader.GetDateTime(1).GetType()) throw new Exception("DateTime type non-match");
                                if (reader.GetValue(2).GetType() != reader.GetDecimal(2).GetType()) throw new Exception("Decimal type non-match");

                                switch (n)
                                {
                                    case 0:
                                        if (reader.GetValue(0).GetType() != typeof(long)) throw new Exception("long type non-match");

                                        if (reader.GetValue(0).Equals((long)1) == false) throw new Exception("long value non-match");
                                        if (reader.GetValue(1).Equals(new DateTime(1753, 1, 1)) == false) throw new Exception(String.Format("DateTime value non-match expected {0} got {1}", new DateTime(1753, 1, 1), reader.GetValue(1)));
                                        if (reader.GetValue(2).Equals((Decimal)1.05) == false) throw new Exception("Decimal value non-match");

                                        if (reader.GetValue(0).Equals(reader.GetInt64(0)) == false) throw new Exception(String.Format("long value failed to match itself, {0} and {1}", reader.GetValue(0), reader.GetInt64(0)));
                                        if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new Exception(String.Format("DateTime failed to match itself {0} and {1}", reader.GetValue(1), reader.GetDateTime(1)));
                                        if (reader.GetValue(2).Equals(reader.GetDecimal(2)) == false) throw new Exception(String.Format("Decimal failed to match itself {0} and {1}", reader.GetValue(2), reader.GetDecimal(2)));
                                        break;
                                    case 1:
                                        if (reader.GetValue(0).GetType() != typeof(string)) throw new Exception("String type non-match");
                                        if (reader.GetValue(0).Equals("One") == false) throw new Exception("String value non-match");
                                        if (reader.GetValue(1).Equals(new DateTime(2001, 1, 1)) == false) throw new Exception(String.Format("DateTime value non-match expected {0} got {1}", new DateTime(2001, 1, 1), reader.GetValue(1)));
                                        if (reader.GetValue(2).Equals((Decimal)1.0) == false) throw new Exception("Decimal value non-match");

                                        if (reader.GetString(0) != "One") throw new Exception("String value non-match");
                                        if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new Exception(String.Format("DateTime failed to match itself {0} and {1}", reader.GetValue(1), reader.GetDateTime(1)));
                                        if (reader.GetValue(2).Equals(reader.GetDecimal(2)) == false) throw new Exception(String.Format("Decimal failed to match itself {0} and {1}", reader.GetValue(2), reader.GetDecimal(2)));
                                        break;
                                    case 2:
                                        if (reader.GetValue(0).GetType() != typeof(double)) throw new Exception("Double type non-match");
                                        if (reader.GetValue(0).Equals(1.01) == false) throw new Exception("Double value non-match");
                                        if (reader.GetValue(1).ToString() != now.ToString()) throw new Exception(String.Format("DateTime value non-match, expected {0} got {1}", now, reader.GetValue(1)));
                                        if (reader.GetValue(2).Equals((Decimal)9.91) == false) throw new Exception("Decimal value non-match");

                                        if (reader.GetDouble(0) != 1.01) throw new Exception("Double value non-match");
                                        if (reader.GetValue(1).Equals(reader.GetDateTime(1)) == false) throw new Exception(String.Format("DateTime failed to match itself {0} and {1}", reader.GetValue(1), reader.GetDateTime(1)));
                                        if (reader.GetValue(2).Equals(reader.GetDecimal(2)) == false) throw new Exception(String.Format("Decimal failed to match itself {0} and {1}", reader.GetValue(2), reader.GetDecimal(2)));
                                        break;
                                }
                            }
                        }
                    }
                    finally
                    {
                        System.Threading.Thread.CurrentThread.CurrentCulture = oldculture;
                        System.Threading.Thread.CurrentThread.CurrentUICulture = olduiculture;
                    }
                }
            }
        }


        [TestMethod]
        public void FastInsertMany()
        {
            string tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString + "AutoCommit=false;"))
            {
                _cnn.Open();
                CreateTable(_cnn, tableName);
                StringBuilder builder = new StringBuilder();
                using (DbTransaction dbTrans = _cnn.BeginTransaction())
                {
                    int dtStart;
                    int dtEnd;

                    using (DbCommand cmd = _cnn.CreateCommand())
                    {
                        cmd.Transaction = dbTrans;
                        cmd.CommandText = "INSERT INTO " + tableName + "(Field1) VALUES( @p1 );";
                        DbParameter Field1 = cmd.CreateParameter();

                        Field1.ParameterName = "@p1";
                        cmd.Parameters.Add(Field1);

                        //Console.WriteLine(String.Format("          Fast insert using parameters and prepared statement\r\n          -> (100,000 rows) Begins ... "));
                        dtStart = Environment.TickCount;
                        for (int n = 0; n < 1000; n++)
                        {
                            Field1.Value = n + 200000;
                            cmd.ExecuteNonQuery();
                        }

                        dtEnd = Environment.TickCount;
                        dtEnd -= dtStart;
                        builder.AppendFormat("Ends in {0} ms ... ", (dtEnd));
                    }

                    dtStart = Environment.TickCount;
                    dbTrans.Commit();
                    dtEnd = Environment.TickCount;
                    dtEnd -= dtStart;
                    builder.AppendFormat("Commits in {0} ms", (dtEnd));
                }
                System.Diagnostics.Debug.WriteLine(builder.ToString());
            }

            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Field1 FROM " + tableName + ";";
                    DbDataReader reader = cmd.ExecuteReader();
                    int count = 0;
                    while (reader.Read())
                    {
                        Assert.AreEqual(count + 200000, reader.GetInt32(0));
                        count++;
                    }

                    Assert.AreEqual(1000, count);
                }
            }
        }


        [TestMethod]
        public void VerifyInsert()
        {
            string tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                CreateTable(_cnn, tableName);
                InsertTable(_cnn, tableName);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "SELECT Field1, Field2, \"Fiëld3\", \"Fiæld4\", Field5 FROM " + tableName;
                    cmd.Prepare();
                    using (DbDataReader rd = cmd.ExecuteReader())
                    {
                        if (rd.Read())
                        {
                            int Field1 = rd.GetInt32(0);
                            double Field2 = rd.GetDouble(1);
                            string Field3 = rd.GetString(2);
                            string Field4 = rd.GetString(3).TrimEnd();
                            DateTime Field5 = rd.GetDateTime(4);

                            if (Field1 != 1) throw new Exception(String.Format("Field1 {0} did not match {1}", Field1, 1));
                            if (Field2 != 3.14159) throw new Exception(String.Format("Field2 {0} did not match {1}", Field2, 3.14159));
                            if (Field3 != "Fiëld3") throw new Exception(String.Format("Field3 {0} did not match {1}", Field3, "Fiëld3"));
                            if (Field4 != "Fiæld4") throw new Exception(String.Format("Field4 {0} did not match {1}", Field4, "Fiæld4"));
                            if (Field5.CompareTo(DateTime.Parse("2005-01-01 13:49:00")) != 0) throw new Exception(String.Format("Field5 {0} did not match {1}", Field4, DateTime.Parse("2005-01-01 13:49:00")));

                            if (!rd.GetName(0).Equals("Field1", StringComparison.OrdinalIgnoreCase)) throw new Exception("Non-Match column name Field1");
                            if (!rd.GetName(1).Equals("Field2", StringComparison.OrdinalIgnoreCase)) throw new Exception("Non-Match column name Field2");
                            if (rd.GetName(2) != "Fiëld3") throw new Exception("Non-Match column name Field3");
                            if (rd.GetName(3) != "Fiæld4") throw new Exception("Non-Match column name Field4");
                            if (!rd.GetName(4).Equals("Field5", StringComparison.OrdinalIgnoreCase)) throw new Exception("Non-Match column name Field5");

                            Field1 = (int)rd["Field1"];
                            Field2 = (double)rd["Field2"];
                            Field3 = (string)rd["Fiëld3"];
                            Field4 = ((string)rd["Fiæld4"]).TrimEnd();
                            Field5 = (DateTime)rd["Field5"];

                            if (Field1 != 1) throw new Exception(String.Format("Field1 {0} did not match {1}", Field1, 1));
                            if (Field2 != 3.14159) throw new Exception(String.Format("Field2 {0} did not match {1}", Field2, 3.14159));
                            if (Field3 != "Fiëld3") throw new Exception(String.Format("Field3 {0} did not match {1}", Field3, "Fiëld3"));
                            if (Field4 != "Fiæld4") throw new Exception(String.Format("Field4 {0} did not match {1}", Field4, "Fiæld4"));
                            if (Field5.CompareTo(DateTime.Parse("2005-01-01 13:49:00")) != 0) throw new Exception(String.Format("Field5 {0} did not match {1}", Field4, DateTime.Parse("2005-01-01 13:49:00")));

                        }
                        else throw new Exception("No data in table");
                    }
                }
            }
        }

        [TestMethod]
        public void ParameterizedInsertMissingParams()
        {
            string tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                CreateTable(_cnn, tableName);
                InsertTable(_cnn, tableName);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO TestCase(Field1, Field2, \"Fiëld3\", \"Fiæld4\", Field5) VALUES(@p1,@p2,@p3,@p4,@p5)";
                    DbParameter Field1 = cmd.CreateParameter();
                    DbParameter Field2 = cmd.CreateParameter();
                    DbParameter Field3 = cmd.CreateParameter();
                    DbParameter Field4 = cmd.CreateParameter();
                    DbParameter Field5 = cmd.CreateParameter();

                    Field1.ParameterName = "@p1";
                    Field2.ParameterName = "@p2";
                    Field3.ParameterName = "@p3";
                    Field4.ParameterName = "@p4";
                    Field5.ParameterName = "@p5";

                    Field1.DbType = System.Data.DbType.Int32;

                    Field1.Value = 2;
                    Field2.Value = 3.14159;
                    Field3.Value = "Field3 Param";
                    Field4.Value = "Field4 Par";
                    Field5.Value = DateTime.Now;

                    cmd.Parameters.Add(Field1);
                    cmd.Parameters.Add(Field2);
                    cmd.Parameters.Add(Field3);
                    cmd.Parameters.Add(Field4);

                    // Assertion here, not enough parameters
                    try
                    {
                        cmd.ExecuteNonQuery();
                        throw new Exception("Executed with a missing parameter");
                    }
                    catch (Exception) // Expected
                    {
                    }
                }
            }
        }


        [TestMethod]
        public void MultiStepReaderTest()
        {
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
            {
                _cnn.Open();
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    //results of the last executed statement is available
                    cmd.CommandText = "CREATE TABLE stepreader (id int primary key);INSERT INTO stepreader values(1);UPDATE stepreader set id = id + 1;SELECT * FROM stepreader;";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read() == false) throw new Exception("Failed to read from the table");
                        if (reader.GetInt32(0) != 2) throw new Exception(String.Format("Expected {0} got {1}", 2, reader.GetInt32(0)));
                    }
                    cmd.CommandText = "SELECT * FROM stepreader";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read() == false) throw new Exception("Failed to read from the table");
                        if (reader.GetInt32(0) != 2) throw new Exception(String.Format("Expected {0} got {1}", 2, reader.GetInt32(0)));
                    }
                }
            }
        }

        internal class MTTest
        {
            internal DbConnection cnn;
            internal Exception e;
            internal System.Threading.Thread t;
            internal int value;
        }

        [TestMethod]
        public void MultithreadingTest()
        {
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString + "AutoCommit=false;"))
            {
                _cnn.Open();
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    cmd.CommandText = "CREATE TABLE MultiThreadedTest(ID int identity primary key, ThreadId int, MyValue int)";
                    cmd.ExecuteNonQuery();
                }
            }

            MTTest[] arr = new MTTest[8];

            for (int n = 0; n < arr.Length; n++)
            {
                arr[n] = new MTTest();
                arr[n].t = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(MultithreadedTestThread));
                arr[n].t.IsBackground = true;
                arr[n].cnn = new EffiProzDBConnection(connString + "AutoCommit=false;");
                arr[n].t.Start(arr[n]);
            }

            System.Threading.Thread.Sleep(8000);
            bool failed = false;
            Exception e = null;
            for (int n = 0; n < arr.Length; n++)
            {
                if (arr[n].t.Join(0) == false)
                {
                    failed = true;
                    arr[n].t.Abort();
                }
                if (arr[n].e != null) e = arr[n].e;
                arr[n].cnn.Dispose();
            }
            if (failed) throw new Exception("One or more threads deadlocked");
            if (e != null)
                throw e;
        }

        internal void MultithreadedTestThread(object obj)
        {
            MTTest test = obj as MTTest;

            if (test.cnn.State != ConnectionState.Open)
                test.cnn.Open();

            int start = Environment.TickCount;
            try
            {
                using (DbCommand cmd = test.cnn.CreateCommand())
                {
                    while (Environment.TickCount - start < 2000)
                    {
                        using (DbTransaction trans = test.cnn.BeginTransaction())
                        {
                            cmd.CommandText = String.Format("SELECT * FROM MultiThreadedTest WHERE ThreadId = {0}", test.t.ManagedThreadId);
                            cmd.Transaction = trans;
                            using (DbDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    test.value += Convert.ToInt32(reader[2]);
                                }
                            }
                            cmd.CommandText = String.Format("INSERT INTO MultiThreadedTest(ThreadId, MyValue) VALUES({0}, {1})", test.t.ManagedThreadId, Environment.TickCount);
                            cmd.ExecuteNonQuery();

                            trans.Commit();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                test.e = e;
            }
        }

        internal void DataAdapter(bool bWithIdentity)
        {
            DbProviderFactory _fact = new EffiProzDBFactory();
            StringBuilder builder = new StringBuilder();

            String tableName = TestHelper.GetNewTableName();
            using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString+"AutoCommit=false;"))
            {
                _cnn.Open();
                CreateTable(_cnn, tableName);
                InsertTable(_cnn, tableName);
                using (DbTransaction dbTrans = _cnn.BeginTransaction())
                {
                    using (DbDataAdapter adp = _fact.CreateDataAdapter())
                    {
                        using (DbCommand cmd = _cnn.CreateCommand())
                        {
                            cmd.Transaction = dbTrans;
                            cmd.CommandText = "SELECT * FROM " + tableName + " WHERE 1 = 2";
                            adp.SelectCommand = cmd;

                            using (DbCommandBuilder bld = _fact.CreateCommandBuilder())
                            {
                                bld.DataAdapter = adp;
                                using (adp.InsertCommand = (DbCommand)((ICloneable)bld.GetInsertCommand()).Clone())
                                {
                                    if (bWithIdentity)
                                    {
                                        adp.InsertCommand.CommandText += ";";
                                        adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                                    }
                                    bld.DataAdapter = null;

                                    using (DataTable tbl = new DataTable())
                                    {
                                        adp.Fill(tbl);
                                        for (int n = 0; n < 100; n++)
                                        {
                                            DataRow row = tbl.NewRow();
                                            row[1] = n + (50000 * ((bWithIdentity == true) ? 2 : 1));
                                            tbl.Rows.Add(row);
                                        }

                                        //Console.WriteLine(String.Format("          Inserting using CommandBuilder and DataAdapter\r\n          ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
                                        int dtStart = Environment.TickCount;
                                        adp.Update(tbl);
                                        int dtEnd = Environment.TickCount;
                                        dtEnd -= dtStart;
                                        builder.AppendFormat("Insert Ends in {0} ms ... ", (dtEnd));

                                        dtStart = Environment.TickCount;
                                        dbTrans.Commit();
                                        dtEnd = Environment.TickCount;
                                        dtEnd -= dtStart;
                                        builder.AppendFormat("Commits in {0} ms", (dtEnd));


                                        if (bWithIdentity)
                                        {
                                            using (DataTable tbl2 = new DataTable())
                                            {
                                                adp.SelectCommand.CommandText = "SELECT * FROM " + tableName + " WHERE Field1 BETWEEN 100000 AND 199999 ORDER BY Field1";
                                                adp.Fill(tbl2);

                                                if (tbl2.Rows.Count != tbl.Rows.Count) throw new Exception("Selected data doesn't match updated data!");

                                                //for (int n = 0; n < tbl.Rows.Count; n++)
                                                //{
                                                //    if (tbl.Rows[n][0].Equals(tbl2.Rows[n][0]) == false)
                                                //        throw new Exception("Fetched identity doesn't match selected identity!");
                                                //}
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            System.Diagnostics.Debug.WriteLine(builder.ToString());
        }

        [TestMethod]
        public void DataAdapterTest()
        {
            DataAdapter(false);
        }

        [TestMethod]
        public void DataAdapterWithIdentityFetch()
        {
            DataAdapter(true);
        }

        //[TestMethod]
        //public void TTTT()
        //{
        //        String tableName = TestHelper.GetNewTableName();
        //        using (EffiProzDBConnection _cnn = new EffiProzDBConnection(connString))
        //        {
        //            _cnn.Open();
        //            CreateTable(_cnn, tableName);
        //            InsertTable(_cnn, tableName);
        //            DbCommand cmd = _cnn.CreateCommand();
        //            cmd.CommandText ="SELECT * FROM "+tableName+";";
        //            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
        //            DataTable dt = reader.GetSchemaTable();
        //            PrintDT(dt);

        //        }
        //}

        [TestMethod]
        public void DistributedTransactionCommitTest()
        {
            DbProviderFactory factory = new EffiProzDBFactory();
            string table = TestHelper.GetNewTableName();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "TStestDB1";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.AutoCommit = false;
            string cnnStr1 = csb.ConnectionString;

            csb.DataSource = "TStestDB2";
            string cnnStr2 = csb.ConnectionString;


            using (TransactionScope scope = new TransactionScope())
            {
                using (DbConnection cnn1 = factory.CreateConnection())
                {
                    cnn1.ConnectionString = cnnStr1;
                    cnn1.Open();
                    CreateTable(cnn1, table);
                    InsertTable(cnn1, table);
                } //Hidden connection should still be active

                using (DbConnection cnn2 = factory.CreateConnection())
                {
                    cnn2.ConnectionString = cnnStr2;
                    cnn2.Open();
                    CreateTable(cnn2, table);
                    InsertTable(cnn2, table);
                } //Hidden connection should still be active

                scope.Complete(); // commit both sessions;
            }

            //verify
            using (DbConnection cnn1 = factory.CreateConnection())
            {
                cnn1.ConnectionString = cnnStr1;
                cnn1.Open();

                DbCommand command = cnn1.CreateCommand();
                command.CommandText = "SELECT * FROM " + table;
                DbDataReader reader = command.ExecuteReader();

                int count = 0;
                while (reader.Read())
                    count++;
                Assert.AreEqual(1, count);
            }

            //verify
            using (DbConnection cnn2 = factory.CreateConnection())
            {
                cnn2.ConnectionString = cnnStr2;
                cnn2.Open();

                DbCommand command = cnn2.CreateCommand();
                command.CommandText = "SELECT * FROM " + table;
                DbDataReader reader = command.ExecuteReader();

                int count = 0;
                while (reader.Read())
                    count++;
                Assert.AreEqual(1, count);
            }

        }


        [TestMethod]
        public void DistributedTransactionRollbackTest()
        {
            DbProviderFactory factory = new EffiProzDBFactory();
            string table = TestHelper.GetNewTableName();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "TSRtestDB1";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Enlist = true;
            csb.AutoCommit = false;
            string cnnStr1 = csb.ConnectionString;

            csb.DataSource = "TSRtestDB2";
            string cnnStr2 = csb.ConnectionString;

            DbConnection _cnn1 = factory.CreateConnection();
            _cnn1.ConnectionString = cnnStr1;
            _cnn1.Open();
            DbConnection _cnn2 = factory.CreateConnection();
            _cnn2.ConnectionString = cnnStr2;
            _cnn2.Open();

            CreateTable(_cnn1, table);
            CreateTable(_cnn2, table);


            using (TransactionScope scope = new TransactionScope())
            {
                using (DbConnection cnn1 = factory.CreateConnection())
                {
                    cnn1.ConnectionString = cnnStr1;
                    cnn1.Open();
                    
                    InsertTable(cnn1, table);
                } //Hidden connection should still be active

                using (DbConnection cnn2 = factory.CreateConnection())
                {
                    cnn2.ConnectionString = cnnStr2;
                    cnn2.Open();
                    
                    InsertTable(cnn2, table);
                } //Hidden connection should still be active

                // Abort both sessions;
            }

            //verify         

            DbCommand command = _cnn1.CreateCommand();
            command.CommandText = "SELECT * FROM " + table;
            DbDataReader reader = command.ExecuteReader();

            int count = 0;
            while (reader.Read())
                count++;
            Assert.AreEqual(0, count);

            //verify         

            DbCommand command2 = _cnn2.CreateCommand();
            command2.CommandText = "SELECT * FROM " + table;
            DbDataReader reader2 = command2.ExecuteReader();

            int count2 = 0;
            while (reader2.Read())
                count++;
            Assert.AreEqual(0, count2);

            _cnn1.Close();
            _cnn2.Close();

        }

        [TestMethod]
        public void DistributedTransactionRollbackTest2()
        {
            DbProviderFactory factory = new EffiProzDBFactory();
            string table = TestHelper.GetNewTableName();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "TSR2testDB1";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Enlist = true;
            csb.AutoCommit = false;
            string cnnStr1 = csb.ConnectionString;

            csb.DataSource = "TSR2testDB2";
            string cnnStr2 = csb.ConnectionString;

            DbConnection cnn1 = new EffiProzDBConnection(cnnStr1);
            DbConnection cnn2 = new EffiProzDBConnection(cnnStr2);
            DbConnection _cnn1 = factory.CreateConnection();
            _cnn1.ConnectionString = cnnStr1;
            _cnn1.Open();
            DbConnection _cnn2 = factory.CreateConnection();
            _cnn2.ConnectionString = cnnStr2;
            _cnn2.Open();

            CreateTable(_cnn1, table);
            CreateTable(_cnn2, table);

            using (TransactionScope scope = new TransactionScope())
            {

                cnn1.Open();
                InsertTable(cnn1, table);

                cnn2.Open();
                InsertTable(cnn2, table);

                // Abort both sessions;
            }

            //verify         

            DbCommand command = _cnn1.CreateCommand();
            command.CommandText = "SELECT * FROM " + table;
            DbDataReader reader = command.ExecuteReader();

            int count = 0;
            while (reader.Read())
                count++;
            Assert.AreEqual(0, count);

            //verify         

            DbCommand command2 = _cnn2.CreateCommand();
            command2.CommandText = "SELECT * FROM " + table;
            DbDataReader reader2 = command2.ExecuteReader();

            int count2 = 0;
            while (reader2.Read())
                count++;
            Assert.AreEqual(0, count2);

            cnn1.Close();
            cnn2.Close();
            _cnn1.Close();
            _cnn2.Close();

        }


        /// <summary>
        /// Make sure our implementation of ClearPool() behaves exactly as the SqlClient version is documented to behave.
        /// </summary>
        [TestMethod]
        public void ClearPoolTest()
        {
            string table = "clearpool";

            DbProviderFactory factory = new EffiProzDBFactory();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "ClearPool1";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Pooling = true;
            csb.AutoCommit = false;
            string cnnStr = csb.ConnectionString;

            using (DbConnection _cnn = new EffiProzDBConnection(cnnStr))
            {
                _cnn.Open();
                string sql = String.Format("CREATE TEMP TABLE {0}(id int primary key) ON COMMIT PRESERVE ROWS; INSERT INTO {0}(id) VALUES(1);", table);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    // Create a temp table in the main connection so we can confirm our new connections are using true new connections
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }

            EffiProzDBConnection.ClearAllPools();

            for (int n = 0; n < 10; n++)
            {
                using (DbConnection newcnn = new EffiProzDBConnection(cnnStr))
                {
                    if (newcnn.State != ConnectionState.Open) newcnn.Open();
                    using (DbCommand cmd = newcnn.CreateCommand())
                    {
                        // If the pool is properly implemented and the pooled connection properly destroyed, this command will succeed.
                        // If the new connection was obtained from the pool even after we cleared it, then this table will already exist
                        // and the test fails.

                        cmd.CommandText = "INSERT INTO " + table + "(id) VALUES("+n+100+");";
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "SELECT count(*) FROM " + table + ";";
                        int count = (int)cmd.ExecuteScalar();

                        Assert.AreEqual(1, count);

                       
                    }
                    // Try and clear the pool associated with this file
                     newcnn.GetType().InvokeMember("ClearPool", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, null, new object[] { newcnn });

                    // At this point when the connection is destroyed, it should not be returned to the pool, but instead disposed.
                }
            }
        }

        /// <summary>
        /// This tests ClearAllPools() functionality.  Makes sure that the pool is working properly and clearing properly.
        /// </summary>
        [TestMethod]
        public void ClearAllPoolsTest()
        {
            DbProviderFactory factory = new EffiProzDBFactory();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "ClearPool2";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Pooling = true;       
            string cnnStr = csb.ConnectionString;

            string table = "clearpool";
            string temp = "TEMP";

            DbConnection _cnn = factory.CreateConnection();
            _cnn.ConnectionString = cnnStr;
            _cnn.Open();

            string sql = String.Format("CREATE {0} TABLE {1}(id int primary key)  ON COMMIT PRESERVE ROWS;", temp, table);

            _cnn.GetType().InvokeMember("ClearAllPools", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, null, null);

            DbConnection[] arr = new DbConnection[10];

            try
            {
                // Create 10 connections and create temporary tables on them
                for (int n = 0; n < 10; n++)
                {
                    arr[n] = new EffiProzDBConnection(cnnStr);
                    if (arr[n].State != ConnectionState.Open) arr[n].Open();

                    using (DbCommand cmd = arr[n].CreateCommand())
                    {
                        try
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception) { }

                        cmd.CommandText = String.Format("INSERT INTO {1} (id) VALUES({0})", n, table);
                        cmd.ExecuteNonQuery();
                    }

                    switch (n)
                    {
                        case 2: // Put this one back into the pool
                            arr[n].Dispose();
                            arr[n] = null;
                            break;
                        case 4:
                            // Clear all the pools
                            _cnn.GetType().InvokeMember("ClearAllPools", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, null, null);
                            break;
                    }
                }

                // Now close all the connections.  Only the last 5 should go into the pool
                for (int n = 0; n < 10; n++)
                {
                    if (arr[n] != null)
                    {
                        arr[n].Dispose();
                        arr[n] = null;
                    }
                }

                // Open 10 connections.  They should either have a clearpool containing an id of 5 or greater,
                // or should have no clearpool table at all.
                for (int n = 0; n < 10; n++)
                {
                    arr[n] = new EffiProzDBConnection(cnnStr);
                    if (arr[n].State != ConnectionState.Open) arr[n].Open();

                    using (DbCommand cmd = arr[n].CreateCommand())
                    {
                        try
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                        catch (Exception) { }
                        cmd.CommandText = String.Format("SELECT id FROM {0}", table);
                        object o = cmd.ExecuteScalar();

                        if (o == null || o == DBNull.Value)
                            continue; // No data in the table at all, which means we must've just created it -- connection wasn't part of the pool

                        if (Convert.ToInt32(o) < 5)
                            throw new Exception("Unexpected data returned from table!");
                    }
                }

                // Clear all the pools
                _cnn.GetType().InvokeMember("ClearAllPools", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, null, null);

            }
            finally
            {
                // Close all the connections
                for (int n = 0; n < 10; n++)
                {
                    if (arr[n] != null)
                    {
                        arr[n].Dispose();
                        arr[n] = null;
                    }
                }
                // Clear all the pools
                _cnn.GetType().InvokeMember("ClearAllPools", System.Reflection.BindingFlags.Static | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, null, null);
               
            }
        }


        /// <summary>
        /// Checks to make sure transactions are rolled back before a connection goes back onto the pool
        /// </summary>
        [TestMethod]
        public void PoolingWithStealthTransactionTest()
        {
            DbProviderFactory factory = new EffiProzDBFactory();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "ClearPool3";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Pooling = true;
            csb.AutoCommit = false;
            string cnnStr = csb.ConnectionString;

            string table = "clearpool";
 

            using (DbConnection _cnn = new EffiProzDBConnection(cnnStr))
            {
                _cnn.Open();
                string sql = String.Format("CREATE TABLE {0}(id int primary key) ;", table);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    // Create a temp table in the main connection so we can confirm our new connections are using true new connections
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }


            for (int n = 0; n < 100; n++)
            {
                using (DbConnection newcnn = new EffiProzDBConnection(cnnStr))
                {
                    if (newcnn.State != ConnectionState.Open) newcnn.Open();
                    using (DbCommand cmd = newcnn.CreateCommand())
                    {              
                        cmd.CommandText = "INSERT INTO "+table+"(id) VALUES(1)";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }


        /// <summary>
        /// Checks to make sure transactions are rolled back before a connection goes back onto the pool
        /// </summary>
        [TestMethod]
        public void PoolingWithTransactionTest()
        {
            DbProviderFactory factory = new EffiProzDBFactory();

            EffiProzDBConnectionStringBuilder csb =
                   (EffiProzDBConnectionStringBuilder)factory.CreateConnectionStringBuilder();
            csb.DataSource = "ClearPool4";
            csb.Type = "Memory";
            csb.User = "sa";
            csb.Password = "";
            csb.Pooling = true;
            csb.AutoCommit = false;
            string cnnStr = csb.ConnectionString;

            string table = "clearpool";


            using (DbConnection _cnn = new EffiProzDBConnection(cnnStr))
            {
                _cnn.Open();
                string sql = String.Format("CREATE TABLE {0}(id int primary key) ;", table);
                using (DbCommand cmd = _cnn.CreateCommand())
                {
                    // Create a temp table in the main connection so we can confirm our new connections are using true new connections
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
            }
           
            for (int n = 0; n < 100; n++)
            {
                using (DbConnection newcnn =new EffiProzDBConnection(cnnStr))
                {
                    if (newcnn.State != ConnectionState.Open) newcnn.Open();
                    DbTransaction trans = newcnn.BeginTransaction();
                    using (DbCommand cmd = newcnn.CreateCommand())
                    {
                        cmd.Transaction = trans;
                        cmd.CommandText = "INSERT INTO " + table + "(id) VALUES(1)";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

    }
}
